package modele;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashSet;
import java.util.TreeSet;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DAO {
    public static void charge(Pays p)  {
        try {
            Connection con;
            java.sql.Statement req;
            java.sql.Statement req2;
            java.sql.Statement req3;
            
            con = Connect.get();
            req = con.createStatement();
            
            req2 = con.createStatement();
            ResultSet rs2 = req.executeQuery("SELECT DISTINCT specialitecomplementaire FROM medecin");
            Collection<Spe> lesSpes = new TreeSet<Spe>();
            while (rs2.next()) {
                if (rs2.getString("specialitecomplementaire")!=null) {
                    Spe s = new Spe(rs2.getString("specialitecomplementaire"));
                    lesSpes.add(s);
                }
            }
            req3 = con.createStatement();
            ResultSet rs3 = req.executeQuery("SELECT DISTINCT departement FROM medecin");
            Collection<Dep> lesDeps = new TreeSet<Dep>();
            while (rs3.next()) {
                Dep d = new Dep(rs3.getString("departement"));
                lesDeps.add(d);
            }
            ResultSet rs = req.executeQuery("SELECT * FROM medecin");
            Collection<Med> lesMeds = new TreeSet<Med>();
            while (rs.next()) {
                Med m = new Med(rs.getString("nom"), rs.getString("prenom"), rs.getString("adresse"),
                        rs.getString("tel"), rs.getString("specialitecomplementaire"), rs.getString("id"),
                        rs.getString("departement"));
                for(Dep d : lesDeps) {
                    if(d.getNum().equals(rs.getString("departement"))) {
                        d.addMed(m);
                    }
                }
                for(Spe s : lesSpes) {
                    if(s.getLibelle().equals(rs.getString("specialitecomplementaire"))) {
                        s.addMed(m);
                    }
                }
                lesMeds.add(m);
            }
            p.setLesDeps(lesDeps);
            p.setLesSpes(lesSpes);
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
//    
//    public static void getLesDeps(Pays p)  {
//        try {
//            Connection con;
//            java.sql.Statement req;
//            java.sql.Statement req2;
//            
//            con = Connect.get();
//            req = con.createStatement();
//            ResultSet rs = req.executeQuery("SELECT DISTINCT departement FROM medecin");
//            Collection<Dep> lesDeps = new TreeSet<Dep>();
//            while (rs.next()) {
//                
//                req2 = con.createStatement();
//                ResultSet rs2 = req2.executeQuery("SELECT * FROM medecin WHERE departement='"+
//                        rs.getString("departement")+"'");
//                Collection<Med> lesMeds = new TreeSet<Med>();
//                while (rs2.next()) {
//                    Med m = new Med(rs2.getString("nom"), rs2.getString("prenom"), rs2.getString("adresse"),
//                            rs2.getString("tel"), rs2.getString("specialitecomplementaire"), rs2.getString("id"));
//                    lesMeds.add(m);
//                }
//                rs2.close();
//                req2.close();
//                Dep d = new Dep(rs.getString("departement"), lesMeds);
//                lesDeps.add(d);
//            }
//            rs.close();
//            req.close();
//            p.setLesDeps(lesDeps);
//        } catch (SQLException ex) {
//            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
//        }
//
//    }
}